Mikmak MySQL DML Country

Probleem

We moeten de gegevens van een land kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam en van de code. Voor het maken van de stored procedures baseren we ons op het Mikmak logisch model.

Design

Oplossing

Insert

De Insert stored procedure voegt een nieuwe rij toe. In de DDL hebben we de Id kolom gedeclareerd als een autoincrement kolom. Dat wil zeggen dat de waarde met 1 wordt vermeerderd als we een rij toevoegen. Als vanuit C# of PHP een call maken naar deze procedure kan het nodig zijn om de nieuwe Id te kennen. Vermits het een stored procedure is en geen functie, kan deze geen waarde retourneren en hebben we een OUTPUT parameter nodig om de waarde van de nieuwe idee naar C# of PHP terug te sturen. In de parameterlijst voegen we dus een OUTPUT parameter toe met de naam Id waarin we de waarde van de Id kolom stoppen van de nieuw toegevoegde rij.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:40 PM
-- DML Insert Stored Procedure for Country 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountryInsert;
DELIMITER //
CREATE PROCEDURE `CountryInsert`
(
	IN pCode NVARCHAR (2) ,
	IN pLatitude FLOAT ,
	IN pLongitude FLOAT ,
	IN pName NVARCHAR (255) ,
	IN pShippingCostMultiplier FLOAT ,
	OUT pId INT 
)
BEGIN
INSERT INTO `Country`
	(
		`Country`.`Code`,
		`Country`.`Latitude`,
		`Country`.`Longitude`,
		`Country`.`Name`,
		`Country`.`ShippingCostMultiplier`
	)
	VALUES
	(
		pCode,
		pLatitude,
		pLongitude,
		pName,
		pShippingCostMultiplier
	);
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

Je kan een lijst met gemaakte stored procedures opvragen met:

show procedure status;

We proberen de Insert stored procedure uit. We beginnen met een variabele te declareren die de output parameter van de stored procedure moet opvangen en vervolgens tonen we de waarde ervan met de PRINT instructie. Als we die test twee keer na elkaar uitvoeren, krijgen we een foutmelding omdat we geen twee keer dezelfde code en/of naam kunnen toevoegen. Dat komt door de UNIQUE constraint op de kolom Code en Name.

call CountryInsert ('BE', 56, 50.8333, 'Belgium', 1, @NewId);
select @NewId;
call CountryInsert ('BZ',84, 17.25, 'Belize', 1.5, @NewId);
select @NewId;
call CountryInsert ('BJ', 204, 9.5, 'Benin', 1.5, @NewId);
select @NewId;
call CountryInsert ('BM', 60, 32.3333, 'Bermuda', 1.5, @NewId);
select @NewId;
call CountryInsert ('BT', 64, 27.5, 'Bhutan', 1.5, @NewId);
select @NewId;
call CountryInsert ('BO', 68, -17, 'Bolivia; Plurinational State of', 1.5, @NewId);
select @NewId;
call CountryInsert ('BA', 70, 44, 'Bosnia and Herzegovina', 1.5, @NewId);
select @NewId;
call CountryInsert ('BW', 72, -22, 'Botswana', 1.5, @NewId);
select @NewId;
call CountryInsert ('BV', 74, -54.4333, 'Bouvet Island', 1.5, @NewId);
select @NewId;
call CountryInsert ('BR', 76, -10, 'Brazil', 1.5, @NewId);
select @NewId;
call CountryInsert ('NL', 52.5, 5.75, 'Netherlands', 1, @NewId);
select @NewId;

Update

De Update stored procedure neemt de Id van de rij die geüpdated moet worden als argument.

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:40 PM
-- DML Update Stored Procedure for Country
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountryUpdate;
DELIMITER //
CREATE PROCEDURE `CountryUpdate`
(
	pCode NVARCHAR (2) ,
	pLatitude FLOAT ,
	pLongitude FLOAT ,
	pName NVARCHAR (255) ,
	pShippingCostMultiplier FLOAT ,
	pId INT 
)
BEGIN
UPDATE `Country`
	SET
		`Code` = pCode,
		`Latitude` = pLatitude,
		`Longitude` = pLongitude,
		`Name` = pName,
		`ShippingCostMultiplier` = pShippingCostMultiplier
	WHERE `Country`.`Id` = pId;
END //
DELIMITER ;

Een voorbeeld:

call CountryUpdate ('BE', 56, 50.8333, 'België', 1.1, 1);

Deze stored procedure wijzigt de verzendingskost factor. De rest van de kolommen behouden hun oorspronkelijke waarden, maar je moet ze wel meegeven omdat de stored procedures deze als argumenten verwacht.

Delete

De Delete stored procedure neemt de Id van de rij die gedeleted moet worden als argument.

Tekstverwerker, code-editor

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:40 PM
-- DML Delete Stored Procedure for Country 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountryDelete;
DELIMITER //
CREATE PROCEDURE `CountryDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `Country`
	WHERE `Country`.`Id` = pId;
END //
DELIMITER ;

Een voorbeeld:

call CountryDelete(1);

We voegen België opnieuw toe. Let erop dat de Id anders is dan de vorige keer:

SelectOne

De SelectOne stored procedure haald de rij met de opgegeven Id uit de tabel op.

Tekstverwerker, code-editor

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:40 PM
-- DML SelectOne Stored Procedure for Country 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountrySelectOne;
DELIMITER //
CREATE PROCEDURE `CountrySelectOne`
(
	 pId INT 
)
BEGIN
SELECT * FROM `Country`
	WHERE `Country`.`Id` = pId;
END //
DELIMITER ;

Zoals altijd testen we de stored procedure:

call CountrySelectOne(5);

SelectAll

Over de SelectAll valt niet veel te zeggen. De stored procedure neemt geen argumenten aan en retourneert alle rijen uit de tabel.

Tekstverwerker, code-editor

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:40 PM
-- DML SelectAll Stored Procedure for table Country 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountrySelectAll;
DELIMITER //
CREATE PROCEDURE `CountrySelectAll`
(
)
BEGIN
SELECT `Country`.`Code`,
	`Country`.`Name`,
	`Country`.`ShippingCostMultiplier`,
	`Country`.`Id`
	FROM `Country`
	ORDER BY `Name`;
END //
DELIMITER ;

SelectByCode

We voegen ook een stored procedure toe om op de Code van het land te zoeken.

Tekstverwerker, code-editor

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectByCode Stored Procedure for table Country
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountrySelectByCode;
DELIMITER //
CREATE PROCEDURE `CountrySelectByCode`
(
	 pCode NVARCHAR (2) 
)
BEGIN
SELECT `Country`.`Code`,
	`Country`.`Name`,
	`Country`.`ShippingCostMultiplier`,
	`Country`.`Id`

	FROM `Country`
	WHERE `Country`.`Code` = pCode
	ORDER BY `Country`.`Code`;
END //
DELIMITER ;

SelectByName

Voor de volledigheid voegen we tenslotte een stored procedure toe om op de naam van het land te kunnen zoeken.

Tekstverwerker, code-editor

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectByName Stored Procedure for table Country
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS CountrySelectByName;
DELIMITER //
CREATE PROCEDURE `CountrySelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `Country`.`Code`,
	`Country`.`Name`,
	`Country`.`ShippingCostMultiplier`,
	`Country`.`Id`

	FROM `Country`
	WHERE `Country`.`Name` = pName
	ORDER BY `Country`.`Name`;
END //
DELIMITER ;